package com.guodi.model.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.guodi.common.utils.EncodingUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @描述：Excel工具类
 * @作者：彭辉
 * @时间：2020/6/9 11:09
 */
@Slf4j
public class ExcelUtil {

    private static final Integer BIG_EXCEL_SIZE = 10000;


    /**
     * @描述：导出单个工作表Excel
     * @参数：fileName 文件名， sheetName 工作表名称， titles 表头， dataList 数据集
     * @作者：彭辉
     * @时间：2020/6/9 18:00
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, String[] titles, List<Object[]> dataList) {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, sheetName);
        // 创建工作表行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            row.createCell(i).setCellValue(titles[i]);
            sheet.setColumnWidth(i, 20 * 256);
        }
        // 创建数据行
        for (int i = 0; i < dataList.size(); i++) {
            HSSFRow newRow = sheet.createRow(i + 1);
            Object[] datas = dataList.get(i);
            for (int j = 0; j < datas.length; j++) {
                newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
            }
        }
        try {
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls");
//            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET,POST");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @描述：导出多个工作表Excel
     * @参数：fileName 文件名， sheetName 工作表名称数组， titles 表头数组， dataList 数据集数组
     * @作者：彭辉
     * @时间：2020/6/9 18:00
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String[] sheetNames, List<String[]> titleList, List<List<Object[]>> dataList) {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (int i = 0; i < sheetNames.length; i++) {
            // 创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(i, sheetNames[i]);
            // 创建工作表行
            HSSFRow row = sheet.createRow(0);
            String[] titles = titleList.get(i);
            for (int j = 0; j < titles.length; j++) {
                row.createCell(j).setCellValue(titles[j]);
                sheet.setColumnWidth(j, 20 * 256);
            }
            // 创建数据行
            List<Object[]> newDataList = dataList.get(i);
            for (int k = 0; k < newDataList.size(); k++) {
                HSSFRow newRow = sheet.createRow(k + 1);
                Object[] datas = newDataList.get(k);
                for (int g = 0; g < datas.length; g++) {
                    newRow.createCell(g).setCellValue(datas[g] == null ? "" : datas[g].toString());
                }
            }
        }
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @描述：导出工作表Excel
     * @参数：
     * @作者：汤厚韬
     * @时间：2021/1/6 15:00
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        if (list.size() > BIG_EXCEL_SIZE) {
            thousandsBigExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        } else {
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }

    }

    /**
     * @描述：解析Excel获取数据集
     * @作者：彭辉
     * @时间：2019/8/31 10:43
     */
    public static List<List<String>> getExcelData(InputStream inputStream, boolean isE2007) {
        try {
            Workbook workbook;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            //获取第一个表单
            Sheet sheet = workbook.getSheetAt(0);
            List<List<String>> dataList = getSheetData(sheet);
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @描述：根据Excel路径解析并获取Excel数据集
     * @作者：彭辉
     * @时间：2019/9/1 9:52
     */
    public static List<List<String>> getExcelData(String filePath, boolean isToTable) {
        List<List<String>> dataList = null;
        boolean isE2007 = false;
        // 判断是否是excel2007格式
        if (filePath.endsWith(".xlsx")) {
            isE2007 = true;
        }
        HttpURLConnection urlConnection = null;
        try {
            filePath = EncodingUtil.encode(filePath);
            URL httpUrl = new URL(filePath);
            urlConnection = (HttpURLConnection) httpUrl.openConnection();
            Workbook wb;
            // 根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(urlConnection.getInputStream());
            } else {
                wb = new HSSFWorkbook(urlConnection.getInputStream());
            }
            // 获取第一个表单
            Sheet sheet = wb.getSheetAt(0);
            if (isToTable) {
                dataList = shteetToList(sheet);
            } else {
                dataList = getSheetData(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (urlConnection != null) {
                urlConnection.disconnect();
            }
        }
        return dataList;
    }

    /**
     * @描述：根据上传MultipartFile对象获取excel数据集
     * @作者：彭辉
     * @时间：2019/9/1 9:44
     */
    public static List<List<String>> getExcelData(MultipartFile file) {
        try {
            String fileName = file.getOriginalFilename();
            Workbook wb = null;
            InputStream is = file.getInputStream();
            // 根据文件格式(2003或者2007)来初始化
            if (fileName.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = new HSSFWorkbook(is);
            }
            // 默认读取第一个表单
            Sheet sheet = wb.getSheetAt(0);
            // Sheet解析成List数据
            List<List<String>> dataList = getSheetData(sheet);
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @描述：Sheet解析成List数据
     * @作者：彭辉
     * @时间：2019/9/1 9:43
     */
    private static List<List<String>> getSheetData(Sheet sheet) {
        List<List<String>> list = new ArrayList<>();
        int rowCount = sheet.getLastRowNum();

        // 格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < rowCount + 1; i++) {
            Row row = sheet.getRow(i);
            if (null != row) {
                List<String> temp = new ArrayList<>();
                for (int j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
//                    Cell cellValue = row.getCell(j);
//                    if (cellValue == null) {
//                        temp.add("");
//                    } else if (cellValue.getCellType() == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cellValue)) {
//                        temp.add(sdf.format(cellValue.getDateCellValue()));
//                    } else {
//                        temp.add(cellValue.toString());
//                    }
                    temp.add(getCellStringValue(row.getCell(j)));
                }
                if (temp.get(0) == "" && temp.get(1) == "") {
                    break;
                }
                list.add(temp);
            }
        }
        return list;
    }

    public static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        CellType type = cell.getCellType();
        String cellValue;
        switch (type) {
            case BLANK:
                cellValue = "";
                break;
            case _NONE:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "";
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "default";
                break;
        }
        return cellValue.trim();
    }





    /**
     * @描述：获取excel单元格数量(处理合并单元格),
     * @作者：邹艺
     * @时间：2019/11/10 17:27
     */
    private static int getColumnCount(Sheet sheet) {
        int nums = sheet.getRow(0).getPhysicalNumberOfCells();
        for (int j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
            if (isMergedRegion(sheet, 0, j)) {
                int sheetMergeCount = sheet.getNumMergedRegions();
                for (int i = 0; i < sheetMergeCount; i++) {
                    CellRangeAddress range = sheet.getMergedRegion(i);
                    int firstColumn = range.getFirstColumn();
                    int lastColumn = range.getLastColumn();
                    int firstRow = range.getFirstRow();
                    int lastRow = range.getLastRow();
                    if (0 == firstRow && 0 <= lastRow) {
                        if (j >= firstColumn && j <= lastColumn) {
                            //合并的第一个单元格不作判断
                            if (0 == firstRow && j == firstColumn) {
                                nums += lastColumn - firstColumn;
                            }
                        }
                    }
                }
            }
        }
        return nums;
    }

    /**
     * @描述：判断指定的单元格是否是合并单元格
     * @作者：彭辉
     * @时间：2019/9/1 9:45
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     *                    表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     *                    第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     *                    ["0","2","0","2","第一个合并单元格","r","bi"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式,字体样式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     *                    对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写），字体样式（b-加粗，i-斜体，bi或ib-斜体加粗，其他-正常非斜体非加粗），大小写不区分，默认非斜体非加粗
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex){
        exportExcel(response,fileName,sheetName,titleConfigs,dataList,dataBeginIndex,new LinkedList<>());
    }
    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     *                    表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     *                    第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     *                    ["0","2","0","2","第一个合并单元格","r","b"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式,字体样式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     *                    对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写），字体样式（b-加粗，i-斜体，bi或ib-斜体加粗，其他-正常非斜体非加粗），大小写不区分，默认非斜体非加粗
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @参数: colorCellList 单元格填充颜色 例如[["0","2","0","2","#552299"], ["3","5","0","2","#00ffff"]]。对应[起始列, 终止列,起始行, 终止行, 颜色-必须是【#00ffff】这种格式]
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex,List<String[]> colorCellList){
        exportExcel(response,fileName,sheetName,titleConfigs,dataList,dataBeginIndex,colorCellList,new LinkedList<>());
    }
    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     *                    表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     *                    第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     *                    ["0","2","0","2","第一个合并单元格","r","bi"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式,字体样式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     *                    对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写），字体样式（b-加粗，i-斜体，bi或ib-斜体加粗，其他-正常非斜体非加粗），大小写不区分，默认非斜体非加粗
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @参数: colorCellList 单元格填充颜色 例如[["0","2","0","2","#552299"], ["3","5","0","2","#00ffff"]]。对应[起始列, 终止列,起始行, 终止行, 颜色-必须是【#00ffff】这种格式]
     * @参数：alignCellList 单元格对齐列表，例如[["1","6","1","1","c"],["1","1","2","2","r"],["1","1","3","3","l"]]。
     *                      对应[起始列, 终止列,起始行, 终止行, 对齐方式-（c：居中，l左对齐，r右对齐，默认l）这种格式]，对齐方式不区分大小写，
     *                      可以对一排单元格进行设置，例如["1","6","1","1","c"]，将设置第二行第二列至第二行第七列中6个单元格统一设置为居中
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex,List<String[]> colorCellList, List<String[]> alignCellList){
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        short height = 24;
        sheet.setDefaultRowHeightInPoints(height);
        workbook.setSheetName(0, sheetName);
        // 设置表格头，合并单元格
        for (List<String> titleConfig : titleConfigs) {
            mergeCell(sheet, Integer.valueOf(titleConfig.get(0)), Integer.valueOf(titleConfig.get(1))
                    , Integer.valueOf(titleConfig.get(2)), Integer.valueOf(titleConfig.get(3)),titleConfig.get(4)
                    , titleConfig.size() <=5 ? null: titleConfig.get(5), titleConfig.size() <= 6? null: titleConfig.get(6));
        }
        // 创建数据行
        CellStyle hssfCellStyle = createCellStyle(workbook, null);
        for (Object[] datas : dataList) {
            HSSFRow newRow = sheet.createRow(dataBeginIndex++ );
            for (int j = 0; j < datas.length; j++) {
                newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
                newRow.getCell(j).setCellStyle(hssfCellStyle);
            }
        }
        // 单元格颜色
        if(colorCellList != null && !colorCellList.isEmpty()){
            // 单元格填充颜色
            short tIndex = IndexedColors.BLUE.index;
            // 取一个自定义调色板
            HSSFPalette palette = workbook.getCustomPalette();
            Map<String, Short> colorMap = new HashMap<>();
            for(String[] colorCell : colorCellList) {
                short colorIndex = 0;
                String colorHex = colorCell[4];
                if (colorMap.get(colorHex) == null) {
                    colorIndex = tIndex;

                    tIndex ++;
                } else {
                    colorIndex = colorMap.get(colorHex);
                }
                int color1 = Integer.parseInt(colorHex.substring(1, 3), 16);
                int color2 = Integer.parseInt(colorHex.substring(3, 5), 16);
                int color3 = Integer.parseInt(colorHex.substring(5, 7), 16);

                palette.setColorAtIndex(colorIndex, (byte)color1, (byte)color2, (byte)color3);
                HSSFFont font = workbook.createFont();
                font.setFontHeightInPoints((short) 12);
                // 字体颜色
                font.setColor(colorIndex);
                colorMap.put(colorHex, colorIndex);
                // 定义一个单元格样式
                HSSFCellStyle style = workbook.createCellStyle();
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                style.setFont(font);
                // 设置单元格
                int cellRowStart = Integer.valueOf(colorCell[2]);
                int cellRowEnd = Integer.valueOf(colorCell[3]);
                int cellColStart = Integer.valueOf(colorCell[0]);
                int cellColEnd = Integer.valueOf(colorCell[1]);
                // 先遍历行
                for(int i = cellRowStart; i <= cellRowEnd; i++) {
                    // 在遍历列
                    for(int j = cellColStart; j <= cellColEnd; j++) {
                        sheet.getRow(i).getCell(j).setCellStyle(style);
                    }
                }
            }
        }
        // 单元格对齐方式
        if (alignCellList != null && !alignCellList.isEmpty()){
            for(String[] alignCell : alignCellList) {
                // 设置单元格
                int cellRowStart = Integer.valueOf(alignCell[2]);
                int cellRowEnd = Integer.valueOf(alignCell[3]);
                int cellColStart = Integer.valueOf(alignCell[0]);
                int cellColEnd = Integer.valueOf(alignCell[1]);
                // 定义一个单元格样式
                CellStyle style = createCellStyle(workbook,null);
                if ("c".equalsIgnoreCase(alignCell[4])){
                    style.setAlignment(HorizontalAlignment.CENTER);
                }else if ("r".equalsIgnoreCase(alignCell[4])){
                    style.setAlignment(HorizontalAlignment.RIGHT);
                }else{
                    style.setAlignment(HorizontalAlignment.LEFT);
                }
                // 先遍历行
                for(int i = cellRowStart; i <= cellRowEnd; i++) {
                    // 在遍历列
                    for(int j = cellColStart; j <= cellColEnd; j++) {
                        sheet.getRow(i).getCell(j).setCellStyle(style);
                    }
                }
            }
        }
        //设置自动列宽
        if (!dataList.isEmpty()) {
            setSheetColumnWidth(sheet, dataList);
        }
        // 导出
        try {
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls");
//            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET,POST");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 创建一个通用样式，workbook和sheet有一个不为空就行
     * @param workbook 用于生成样式
     * @param sheet 用于生成样式
     * @return
     */
    private static  CellStyle createCellStyle(Workbook workbook,Sheet sheet){
        workbook = workbook == null? sheet.getWorkbook(): workbook;
        CellStyle style = workbook.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font hssfFont = workbook.createFont();
        hssfFont.setFontHeightInPoints((short) 12);
        style.setFont(hssfFont);
        return style;
    }


    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数:
     * @作者: 汤厚韬
     * @时间: 2021-01-22
     */
    public static HSSFWorkbook exportExcel(String sheetName, List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex,List<String[]> colorCellList){
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        short height = 18;
        sheet.setDefaultRowHeightInPoints(height);
        workbook.setSheetName(0, sheetName);
        // 设置表格头，合并单元格
        for (List<String> titleConfig : titleConfigs) {
            try {
                mergeCell(sheet, Integer.valueOf(titleConfig.get(0)), Integer.valueOf(titleConfig.get(1))
                        , Integer.valueOf(titleConfig.get(2)), Integer.valueOf(titleConfig.get(3)),titleConfig.get(4)
                        , titleConfig.size() <= 5? null: titleConfig.get(5), titleConfig.size() <= 6? null: titleConfig.get(6));
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        // 创建数据行
        HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        for (Object[] datas : dataList) {
            HSSFRow newRow = sheet.createRow(dataBeginIndex++ );
            for (int j = 0; j < datas.length; j++) {
                newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
                newRow.getCell(j).setCellStyle(hssfCellStyle);
            }
        }
        // 单元格填充颜色
        for(String[] colorCell : colorCellList) {
            String colorHex = colorCell[4];
            int color1 = Integer.parseInt(colorHex.substring(1, 3), 16);
            int color2 = Integer.parseInt(colorHex.substring(3, 5), 16);
            int color3 = Integer.parseInt(colorHex.substring(5, 7), 16);
            // 取一个自定义调色板
            HSSFPalette palette = workbook.getCustomPalette();
            palette.setColorAtIndex(IndexedColors.BLUE.index, (byte)color1, (byte)color2, (byte)color3);
            HSSFFont font = workbook.createFont();
            // 字体颜色
            font.setColor(IndexedColors.BLUE.index);
            // 定义一个单元格样式
            HSSFCellStyle style = workbook.createCellStyle();
            style.setFont(font);
            // 设置单元格
            int cellRowStart = Integer.valueOf(colorCell[2]);
            int cellRowEnd = Integer.valueOf(colorCell[3]);
            int cellColStart = Integer.valueOf(colorCell[0]);
            int cellColEnd = Integer.valueOf(colorCell[1]);
            // 先遍历行
            for(int i = cellRowStart; i <= cellRowEnd; i++) {
                // 在遍历列
                for(int j = cellColStart; j <= cellColEnd; j++) {
                    sheet.getRow(i).getCell(j).setCellStyle(style);
                }
            }
        }

        //设置自动列宽
        for (int i = 0; i < dataList.get(0).length; i++) {
            setSheetColumnWidth(sheet, dataList);
        }

        return workbook;
    }


    /**
     * @描述：导出excel，可以有多个sheet工作表
     * @参数： outputStream 输出流，用于接收excel
     * @参数： sheetDataMap 数据集，key为sheet的名字，value为sheet的数据列表
     * @参数： dataBeginIndexMap 数据起始行下标，从0开始，key为sheet的名字，与sheetDataMap的key对应
     * @参数： mergeConfigs 合并单元格配置，key为sheet的名字，与sheetDataMap的key对应。value为合并单元格配置。
     *                    例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     *                    表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     *                    第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     *                    ["0","2","0","2","第一个合并单元格","r","bi"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式,字体样式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     *                    对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写），字体样式（b-加粗，i-斜体，bi或ib-斜体加粗，其他-正常非斜体非加粗），大小写不区分，默认非斜体非加粗
     *          内容对齐方式可以不填，只有5个元素
     * @参数： isFlushAndCloseStream 是否刷新和关闭输出流，如果是false，需要外层自己关闭
     */
    public static void exportExcelXLS(OutputStream outputStream, Map<String,List<Object[]>> sheetDataMap,Map<String,Integer> dataBeginIndexMap
            ,Map<String,List<String[]>> mergeConfigs, boolean isFlushAndCloseStream) throws IOException {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 遍历创建每个工作表
        Set<Map.Entry<String, List<Object[]>>> entrySet = sheetDataMap.entrySet();
        int sheetIndex = 0;
        for(Map.Entry<String, List<Object[]>> sheetData : entrySet) {
            int dataBeginIndex = dataBeginIndexMap.getOrDefault(sheetData.getKey(),0);
            // 创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            short height = 18;
            sheet.setDefaultRowHeightInPoints(height);
            workbook.setSheetName(sheetIndex++, sheetData.getKey());
            // 创建数据行
            HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
            hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont hssfFont = workbook.createFont();
            hssfFont.setFontHeightInPoints((short) 12);
            hssfCellStyle.setFont(hssfFont);
            // 放数据
            for (Object[] datas : sheetData.getValue()) {
                HSSFRow newRow = sheet.createRow(dataBeginIndex++ );
                for (int j = 0; j < datas.length; j++) {
                    newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
                    newRow.getCell(j).setCellStyle(hssfCellStyle);
                }
            }
            // 设置表格头，合并单元格
            List<String[]> mergeCellList = mergeConfigs.getOrDefault(sheetData.getKey(),new LinkedList<>());
            for (String[] mergeCell : mergeCellList) {
                mergeCell(sheet, Integer.valueOf(mergeCell[0]), Integer.valueOf(mergeCell[1])
                        , Integer.valueOf(mergeCell[2]), Integer.valueOf(mergeCell[3]),mergeCell[4], mergeCell.length <= 5? null: mergeCell[5]
                        , mergeCell.length <= 6 ? null: mergeCell[6]);
            }
            //设置自动列宽
            if (!sheetData.getValue().isEmpty()) {
                setSheetColumnWidth(sheet, sheetData.getValue());
            }
        }
        // 写Excel
        workbook.write(outputStream);
        // 关闭流
        if (isFlushAndCloseStream){
            outputStream.flush();
            outputStream.close();
        }
    }


    /**
     * 	@描述：合并单元格
     * 	@参数: sheet
     * 	@参数: startCol 起始列，下标从0开始
     * 	@参数: endCol 终止列
     * 	@参数: startRow 起始行，行下标从0开始
     * 	@参数: endRow 终止行
     * 	@参数: text 合并后的单元格文本
     * 	@参数: align 文本对齐方式（c-居中，l-居左，r-居右），大小写不区分，默认居中
     * 	@参数: textFontStyle 字体样式（b-加粗，i-斜体，bi或ib-斜体加粗，其他-正常非斜体非加粗），大小写不区分，默认非斜体非加粗
     *  @作者: 陈宏成
     *  @时间: 2020-12-22
     * */
    @SuppressWarnings("unused")
    private static void mergeCell(HSSFSheet sheet,int startCol,int endCol,int startRow,int endRow,String text,String align,String textFontStyle) {
        boolean isMerged = false;
        if(startRow == endRow && startCol == endCol){
            // 如果只是一个单元格，没必要合并
            isMerged = false;
        }else{
            // 创建合并单元格
            CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCol, endCol);
            sheet.addMergedRegion(cra);
            isMerged = true;
        }
        // 设置单元格文本
        HSSFRow row = sheet.getRow(startRow) == null? sheet.createRow(startRow):sheet.getRow(startRow);
        HSSFCell cell = row.getCell(startCol) == null? row.createCell(startCol): row.getCell(startCol);
        //合并的单元格样式
        HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
        HSSFFont font = sheet.getWorkbook().createFont();
        style.setFont(font);
        font.setFontHeightInPoints((short) 12);
        // 设置加粗斜体
        if(StringUtils.isNotBlank(textFontStyle) ){
            if (textFontStyle.trim().equalsIgnoreCase("b")){
                font.setBold(true);
            }else if (textFontStyle.trim().equalsIgnoreCase("i")){
                font.setItalic(true);
            }else if (textFontStyle.trim().equalsIgnoreCase("ib") || textFontStyle.trim().equalsIgnoreCase("bi")){
                font.setItalic(true);
                font.setBold(true);
            }else{
                font.setItalic(false);
                font.setBold(false);
            }
        }else{
            font.setItalic(false);
            font.setBold(false);
        }
        //设置居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        if(StringUtils.isNotBlank(align) ){
            if("l".equalsIgnoreCase(align)){
                style.setAlignment(HorizontalAlignment.LEFT);
            }else if ("r".equalsIgnoreCase(align)){
                style.setAlignment(HorizontalAlignment.RIGHT);
            }else{
                style.setAlignment(HorizontalAlignment.CENTER);
            }
        }else{
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        cell.setCellStyle(style);
        cell.setCellValue(text);
    }

    /**
     * @描述：Sheet解析成List数据
     * @作者：彭辉
     * @时间：2019/9/1 9:50
     */
    private static List<List<String>> shteetToList(Sheet sheet) {
        List<List<String>> list = new ArrayList();
        int rowCount = sheet.getLastRowNum();
        int columns = getColumnCount(sheet);
        for (int i = 0; i < rowCount; ++i) {
            List<String> temp = new ArrayList();
            int columnLength;
            if (i == 0) {
                columnLength = sheet.getRow(0).getPhysicalNumberOfCells();
            } else {
                columnLength = columns;
            }
            for (int j = 0; j < columnLength; ++j) {
                temp.add(cellToTd(sheet, i, j));
            }
            list.add(temp);
        }
        return list;
    }

    /**
     * @描述：Cell解析成table的td
     * @作者：彭辉
     * @时间：2019/9/1 9:50
     */
    private static String cellToTd(Sheet sheet, int row, int column) {
        if (sheet.getRow(row).getCell(column) == null) {
            return "";
        }
        Cell cellValue = sheet.getRow(row).getCell(column);
        cellValue.setCellType(CellType.STRING);
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    //合并的第一个单元格不作判断
                    if (row == firstRow && column == firstColumn) {
                        return "<td rowspan='" + (lastRow - firstRow + 1) + "' colspan='" + (lastColumn - firstColumn + 1) + "'>" + cellValue + "</td>";
                    } else {
                        return "";
                    }
                }
            }
        }
        return "<td>" + cellValue + "</td>";
    }


    static void thousandsBigExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams params) {
        Workbook workbook = null;
        int size = list.size();
        int page = size / BIG_EXCEL_SIZE + (size % BIG_EXCEL_SIZE > 0 ? 1 : 0);

        for(int j = 0; j < page; ++j) {
            int toIndex;
            if (j == page - 1) {
                toIndex = size;
            } else {
                toIndex = BIG_EXCEL_SIZE * (j + 1);
            }

            List<?> dataList = list.subList(BIG_EXCEL_SIZE * j, toIndex);
            workbook = ExcelExportUtil.exportBigExcel(params, pojoClass, dataList);
        }

        ExcelExportUtil.closeExportBigExcel();
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException var4) {
            var4.printStackTrace();
        }

    }
    /**
     * @描述:查询合并单元格的数据
     * @入参:
     * @出参:
     * @作者: lzy
     * @日期: 2021/3/18 16:55
     **/
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }
    /**
     * @描述:获取单元格的值（包含合并单元格）
     * @入参:
     * @出参:
     * @作者: lzy
     * @日期: 2021/3/18 16:55
     **/
    public static String getCellValue(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) throws Exception {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for (CellRangeAddress ca : listCombineCell) {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getStringCellValue(fCell);
                    return cellValue;
                }
            }
        }
        return getStringCellValue(cell);
    }
    /**
     * @描述:得到单元格的值
     * @入参:
     * @出参:
     * @作者: lzy
     * @日期: 2021/3/18 16:55
     **/
    private static String getStringCellValue(Cell cell) {
        if (cell != null) {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        }
        return null;
    }

    /**
     * 计算单元格的合适宽度，计算公式：256 * 8 * (字符个数) / 3
     * @param sheet excel 工作部
     * @param dataIndex 第几列，下标从0开始
     * @author 陈宏成
     * @date   2021/12/9
     */
    private static int calculateColumnWidth(Sheet sheet,  int dataIndex){
        int defaultCharNum = 2;
        int maxCharNum = defaultCharNum;
        int lastRowNum = sheet.getLastRowNum();
        for(int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            Cell cell = Objects.isNull(row)? null: row.getCell(dataIndex);
            int length = Objects.isNull(cell)? defaultCharNum: cell.toString().length();
            maxCharNum = Math.max(maxCharNum, length);
        }

        return 256 * 8 * maxCharNum / 3 ;
    }

    /**
     * 设置sheet单元格的宽度
     * @param sheet excel 工作部
     * @param dataList 数据列表
     * @author 陈宏成
     * @date   2021/12/9
     */
    private static void setSheetColumnWidth(Sheet sheet, List<Object[]> dataList){
        if (dataList == null || dataList.isEmpty()){
            return;
        }
        try {
            for (int i = 0,size = dataList.size(); i < size; i++) {
                sheet.autoSizeColumn(i);
                sheet.autoSizeColumn(i, true);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
            }
        }catch (Exception exception){
            log.error("设置单元格宽度错误", exception);
            for (int i = 0,size = dataList.size(); i < size; i++) {
                sheet.setColumnWidth(i, calculateColumnWidth(sheet,  i) );
            }
        }
    }

    /**
     * 合并单元格
     * @入参： sheet
     * @入参： startCol 起始列，下标从0开始
     * @入参： endCol 终止列
     * @入参： startRow 起始行，行下标从0开始
     * @入参： endRow 终止行
     * @入参： text 合并后的单元格文本
     * @入参： XSSFCellStyle 合并单元格样式，默认无
     * @作者：
     * @日期： 2023-04-04
     */
    @SuppressWarnings("unused")
    public static void mergeCell(Sheet sheet, int startCol, int endCol, int startRow, int endRow, String text, XSSFCellStyle cellStyle) {
        boolean isMerged = false;
        if (startRow == endRow && startCol == endCol) {
            // 如果只是一个单元格，没必要合并
            isMerged = false;
        } else {
            // 创建合并单元格
            CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCol, endCol);
            sheet.addMergedRegion(cra);
            isMerged = true;
        }
        // 设置单元格文本
        Row row = sheet.getRow(startRow) == null ? sheet.createRow(startRow) : sheet.getRow(startRow);
        Cell cell = row.getCell(startCol) == null ? row.createCell(startCol) : row.getCell(startCol);
        //合并的单元格样式
        for(int i = startRow; i <= endRow; i++){
            for(int j = startCol; j <= endCol; j++){
                Row  row1 = sheet.getRow(i) == null ? sheet.createRow(i) : sheet.getRow(i);
                Cell cell1 =row1.getCell(j) == null ? row1.createCell(j) : row1.getCell(j);
                cell1.setCellStyle(cellStyle);
            }
        }
//        cell.setCellStyle(cellStyle);
        // 填单元格内容
        cell.setCellValue(text);
    }
}
